package com.etc.team2.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.etc.team2.bean.Line;
import com.etc.team2.bean.Station;
import com.etc.team2.jdbc.JDBCHelper;

public class LineDAO {
	private ResultSet set;

	private JDBCHelper helper;

	public LineDAO() {
		helper = new JDBCHelper();
	}
	

	public List<Line> getAllLine() {// 获取所有路线信息，用于管理员管理所有路线。
		List<Line> lines=new ArrayList<Line>(); 
		String sql1="select * from bus_line_info";
		ResultSet set=helper.executeQueryByPrepareStatement(sql1, null);
		try {
			while (set.next()) {
			    Line line=new Line();
				List<String> stationlists=new ArrayList<String>();
				int id=set.getInt(1);
				String lineName=set.getString(2);
				String firsttime=set.getString(3);
				String lasttime=set.getString(4);
				double price=set.getDouble(5);
				line.setLineId(id);
				line.setLineName(lineName);
				line.setFirstTime(firsttime);
				line.setLastTime(lasttime);
				line.setPrice(price);
				String sql2="select b.stationName from bus_lineandstation_info a ,bus_station_info b where a.stationId=b.stationId and a.lineId="+id;
				ResultSet set2=helper.executeQueryByPrepareStatement(sql2, null);
				while (set2.next()) {
					String stationName=set2.getString(1);
					stationlists.add(stationName);
				}
				line.setStationlist(stationlists);
		      lines.add(line);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return lines;
	}
	
	public Line getLine(String lineName) {// 根据路线名获取站台信息，运用在用户搜索指定路线信息时。
		String sql="select * from bus_line_info  where lineName="+"\'"+lineName+"\'";
		set=helper.executeQueryByPrepareStatement(sql, null);
		
		try {
			while(set.next()){
				Line line=new Line();
				List<String> stationlist=new ArrayList<String>();
				int id = set.getInt(1);
				String lineNameString=set.getString(2);
				String firsttime=set.getString(3);
				String lasttime=set.getString(4);
				double price=set.getDouble(5);
				double interval=set.getDouble(6);
				line.setLineName(lineNameString);
				line.setFirstTime(firsttime);
				line.setLastTime(lasttime);
				line.setPrice(price);
				line.setInterval(interval);
				line.setLineId(id);
				String sql2="select b.stationName from bus_lineandstation_info a,bus_station_info b where a.stationId=b.stationId and a.lineId="+
				id;
				ResultSet set2=helper.executeQueryByPrepareStatement(sql2, null);
				while(set2.next()){
					String stationName=set2.getString(1);
					stationlist.add(stationName);
				}
				line.setStationlist(stationlist);
				return line;
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public boolean deleteLine(int lineId)
	{/*
	 *通过lineId删除线路
	 *注意，字段lineId是bus_line_info和bus_lineandstation_info的外键。
	 *需先删除bus_lineandstation_info中含有此lineId字段的元祖，再删除bus_line_info中含有lineId的元组
	 *删除后返回AllLineServlet
	 *	
	*/
		String sql="delete from bus_lineandstation_info where lineId="+lineId;
		int number=helper.executeUpdateByPrepareStatement(sql, null);
		String sql2="delete from bus_line_info where lineId="+lineId;
		int number2=helper.executeUpdateByPrepareStatement(sql2, null);
		if(number!=0&&number2!=0){
			return true;
		}
		return false;
	}
	
	public boolean addLine(Line line)
	{/*
	*根据接收到的line添加到bus_line_info和bus_lineandstation_info中
	*成功，返回true
	*失败，返回false
	*/
		int reallineId = 0,result=0,stationId = 0;
		String sql="insert into bus_line_info(lineName,firstTime,lastTime,price) values(?,?,?,?)";
		Object[] objects=new Object[]{line.getLineName(),line.getFirstTime(),line.getLastTime(),line.getPrice()};
		int s=helper.executeUpdateByPrepareStatement(sql, objects);
		String lineid="select lineId from bus_line_info where lineName="+"\'"+line.getLineName()+"\'";
		ResultSet lineId=helper.executeQueryByPrepareStatement(lineid, null);
		try {
			if(lineId.next()){
				reallineId=lineId.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		for (int i = 0; i < line.getStationlist().size(); i++) {
			String station="select stationId from bus_station_info where stationName="+"\'"+line.getStationlist().get(i)+"\'";
			set=helper.executeQueryByPrepareStatement(station, null);
			try {
				while(set.next()){
					stationId=set.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			Object[] objects2=new Object[]{reallineId,stationId,i+1,line.getStationlist().size()-i};
			String sql2="insert into bus_lineandstation_info(lineId,stationId,goup,godown) values(?,?,?,?)";
			result=helper.executeUpdateByPrepareStatement(sql2, objects2);
		}
		if(s!=0&&result!=0){
			return true;
		}
		return false;
	}

}
